#! /usr/bin/env python3
# -*- coding:UTF-8 -*-
"""
@Project:   DataCrawler
@FileName:  mysql_example.py 
@Create:    2023/5/29 14:38
@Version:   Python3.9  
@Author:    Jia  
@Descr:     ->python操作mysql的示例
"""
import pymysql
import pandas as pd
import traceback
from Config.log import Logs
from IPython.display import display
from sqlalchemy import create_engine, text

logger = Logs().debug_logger()


class Mysql:
    def __init__(self, db_name):
        """创建数据库，并返回连接对象 游标"""

        self.db = db_name
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database='mysql')
        cursor = conn.cursor()
        cursor.execute("SHOW DATABASES")
        db_list = []
        # 遍历数据库名
        for cur in cursor:
            db_list.append(cur[0])
        if self.db in db_list:
            self.conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database=self.db)
            self.cursor = self.conn.cursor()
        else:
            cursor.execute("CREATE DATABASE " + self.db)
            self.conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database=self.db)
            self.cursor = self.conn.cursor()

    def create_table(self):
        """创建表格"""

        sql = 'drop table if exists`example`'

        sql2 = 'CREATE TABLE`example`(`emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL,'\
               '`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));'
        try:
            self.cursor.execute(sql)
            self.cursor.execute(sql2)
            self.conn.commit()
            logger.info('创建表成功')
        except Exception as e:
            logger.error(e)
            logger.error(traceback.format_exc())
            self.conn.rollback()

    def insert_data(self):
        """向表内插入数据"""

        sql = "INSERT INTO example VALUES(%s,%s,%s,%s)"
        datas = [(10001,'d001','1986-06-26','9999-01-01'), (10002,'d001','1996-08-03','9999-01-01')]
        try:
            # 快速插入
            self.cursor.executemany(sql,datas)
            self.conn.commit()
            logger.info('插入数据成功')
        except Exception as e:
            logger.info(e)
            logger.error(traceback.format_exc())
            self.conn.rollback()

    def search_data(self):
        """查询数据并以图表形式输出"""

        host = '127.0.0.1'
        user = 'root'
        pw = '123456'
        port = 3306
        db = self.db

        try:
            sql = "SELECT * FROM example"
            # engine = create_engine('dialect+driver://username:password@host:port/database')
            engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8' % (user, pw, host, port, db))
            res = pd.read_sql(text(sql), engine.connect())
            display(res)
        except Exception as e:
            logger.error(e)
            logger.error(traceback.format_exc())

    def get_datas(self):
        """获取查询结果"""

        sql = "SELECT * FROM example"
        self.cursor.execute(sql)

        # 返回所有结果以多个元组的方式（（id,name),(id,name)）
        datas = self.cursor.fetchall()

        for a,b,c,d in datas:
            logger.info(f"{str(a)},{b},{c},{d}")


if __name__ == '__main__':
    m = Mysql('python_test')
    # m.create_table()
    # m.insert_data()
    # m.search_data()
    m.get_datas()
    m.cursor.close()
    m.conn.close()
